﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Drawing;
using System.Linq;
using System.Security.Policy;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using web_search;

namespace BookManageSystem
{
    public partial class FormBookBorrowForUser : Form
    {
        public FormBookBorrowForUser()
        {
            InitializeComponent();
        }

        //图书信息 用于修改图书信息
        public static string ISBN = "";
        public static string BookName = "";
        public static float Price;
        private void dgvBook_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void LoadBooks()
        {
            dgvBook.Rows.Clear();

            Daouser dao = new Daouser();
            dao.connect();
            
            //计算同一个ISBN的书对应的在馆数量表ct
            string sql2 = $"select ISBN,COUNT(*) AS CountOfIsbnOne from V_UserBookInfor where BookStatus = 0 Group by ISBN ";

            //选出ISBN表,dt
            string sql = $"select distinct ISBN,BookName,AuthorName,Price from V_UserBookInfor";
            
            //两个表进行表连接,进行右连接，如果某个ISBN号的书数量为0，就用0代替空
            string sql3 = $"select dt.ISBN,dt.BookName,dt.AuthorName,ISNULL(ct.CountOfIsbnOne,0) as countIsbn ,dt.Price " +
                            $"from ({sql2}) AS ct RIGHT OUTER JOIN ({sql}) AS dt ON ct.ISBN=dt.ISBN ";

            SqlDataReader reader = dao.read(sql3);
            
            //放在表里
            while (reader.Read())
            {
                dgvBook.Rows.Add(
                    reader[0].ToString(),
                    reader[1].ToString(),
                    reader[2].ToString(),
                    reader[3].ToString(),
                    reader[4].ToString()
                    );
            }
            reader.Close();
            dao.DaoClose();
        }

        private void dgvBook_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            //获取当前选中的行
            if (dgvBook.CurrentRow == null || dgvBook.CurrentRow.Cells[0].Value == null)
            {
                //MessageBox.Show("选中无效数据！", "消息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            string ISBN_tmp = dgvBook.CurrentRow.Cells[0].Value.ToString();//当前选中的图书ISBN号
            string title = dgvBook.CurrentRow.Cells[1].Value.ToString();//当前选中的图书书名
            //显示选中的图书信息
            Label_ISBN.Text = ISBN_tmp;
            Label_Title.Text = title;
        }

        private void FormCirculation_Load(object sender, EventArgs e)
        {
           
            LoadBooks();
        }

        private void Book_info_Click(object sender, EventArgs e)
        {
            //查看图书详细信息
            if (Label_ISBN.Text == "NULL")
            {
                MessageBox.Show("请选择图书！", "消息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            Daouser dao = new Daouser();
            dao.connect();
            string sql = $"select BookName,ISBN,Price from V_UserBookInfor where ISBN='{Label_ISBN.Text}'";
            SqlDataReader reader = dao.read(sql);
            reader.Read();

            BookName = reader[0].ToString();
            ISBN = reader[1].ToString();
            Price = float.Parse(reader[2].ToString());

            FormBookDetailsForUser form = new FormBookDetailsForUser();
            form.ShowDialog();
        }

        private void button3_Click(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {//搜索图书

            //拿到关键字
            string keyword = SearchtextBox.Text.Trim();

            //根据关键字查询数据库
            Daouser dao = new Daouser();
            dao.connect();

            //计算同一个ISBN的书对应的在馆数量表ct
            string sql2 = $"select ISBN,COUNT(*) AS CountOfIsbnOne from V_UserBookInfor where BookStatus = 0 Group by ISBN ";

            //选出ISBN表,dt
            string sql = $"select distinct ISBN,BookName,AuthorName,Price from V_UserBookInfor";

            string sql3 = $"select dt.ISBN,dt.BookName,dt.AuthorName,ISNULL(ct.CountOfIsbnOne,0) as countIsbn,dt.Price " +
                            $"from ({sql2}) AS ct RIGHT OUTER JOIN ({sql}) AS dt ON ct.ISBN=dt.ISBN ";

            string sql4 = $"select * from ({sql3}) as sch where sch.ISBN like '%{keyword}%' or sch.AuthorName like '%{keyword}%' or sch.BookName like '%{keyword}%' ";

            SqlDataReader reader = dao.read(sql4);


            //清空表格
            dgvBook.Rows.Clear();

            //将查询到的数据显示到表格中
            while (reader.Read())
            {
                dgvBook.Rows.Add(reader[0].ToString(),
                    reader[1].ToString(),
                    reader[2].ToString(),
                    reader[3].ToString(),
                    reader[4].ToString()
                    );
            }
            reader.Close();
            dao.DaoClose();

        }

        private void button4_Click(object sender, EventArgs e)
        {
            //刷新
            LoadBooks();
            MessageBox.Show("刷新成功！", "消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }
}
